﻿using System;
using System.Linq;
using System.Data;
using System.Collections;
using System.Text;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Data.OracleClient;
using Models;
using Utility;
using Service.Interface;
using Repository.Interface;
using SqlSugar;


namespace Service.Impl
{
    public class JobHeadServices : IJobHeadServices
    {
        private IBaseRepository _PackinforRepository;
        private SqlSugarClient _db;
        public JobHeadServices(ISqlSugarClient db, IBaseRepository PackinforRepository)
        {
            _db = db as SqlSugarClient;
            _PackinforRepository = PackinforRepository;
        }

        public bool Mergeinto(List<JobHead> list)
        {
            int count = 0;
            for (int i = 0; i < list.Count; i++)
            {
                string str = $@"
                MERGE INTO JOB_HEAD J 
                USING DUAL ON (J.COMPANY = '{list[i].Company}' 
	                AND J.JOBNUM = '{list[i].JobNum}' 
	                AND PARTNUM = '{list[i].PartNum}' )
                WHEN MATCHED THEN
                UPDATE 
	                SET J.PRODQTY = '{list[i].ProdQty}',
	                J.PRODUCTIONTYPE_C = '{list[i].ProductionType_c}',
	                J.ISREWORK_C = {(list[i].IsRework_c ? 1 : 0)},
	                J.JOBRELEASED = {(list[i].JobReleased ? 1 : 0)},
                    J.REWORKTYPE_C ='{list[i].ReworkType_c}',
                    J.JOBCLOSED = {(list[i].JobClosed ? 1 : 0)},
	                --J.MODIFY_USER = '{list[i].Modify_User}',
	                J.MODIFY_TIME = TO_DATE( '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-mm-dd hh24:mi:ss' ) 
                WHEN NOT MATCHED THEN
	                INSERT ( COMPANY, JOBNUM, PARTNUM, PRODUCTIONTYPE_C, ISREWORK_C, JOBRELEASED, CREATE_TIME, PRODQTY, JOBCLOSED,REWORKTYPE_C)
                    VALUES
	                    (
		                    '{list[i].Company}',
		                    '{list[i].JobNum}',
		                    '{list[i].PartNum}',
		                    '{list[i].ProductionType_c}',
		                    '{(list[i].IsRework_c ? 1 : 0)}',
		                    '{(list[i].JobReleased ? 1 : 0)}',
		                    --'{list[i].Create_User}',
		                    TO_DATE( '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-mm-dd hh24:mi:ss' ),
		                    '{list[i].ProdQty}',
		                    '{(list[i].JobClosed ? 1 : 0)}' ,
                            '{list[i].ReworkType_c}'
	                    )";
                count += _db.GetConnection("OUSB").Ado.ExecuteCommand(str.Replace(@"[\r\n]", ""));
            }
            return list.Count == count ? true : false;
        }

        public bool AddJobHead(List<JobHead> list)
        {
            foreach (JobHead jh in list)
            {
                SaveJobHead(jh, jh.ProductionType_c.ToUpper());
            }
            return true;
        }

        private bool SaveJobHead(JobHead jh, string type)
        {
            bool result = false;
            if (isExistJobHead(jh, type))
            {
                result = UpdateJobHead(jh, type);
            }
            else
            {
                result = InsertJobHead(jh, type);
            }
            return result;
        }

        public bool InsertJobHead(JobHead jH, string type)
        {
            string sql = string.Empty;
            if (type.Contains("OFN"))
            {
                sql = @"INSERT INTO job_head(company,jobnum,partnum,prodqty,productiontype_c,isrework_c,jobreleased,jobclosed,ReworkType_c)
                               VALUES(?company ,?jobnum,?partnum,?prodqty,?productiontype_c,?isrework_c,?jobreleased,?jobclosed,?ReworkType_c)";
                List<MySqlParameter> parms = new List<MySqlParameter>()
                {
                    new MySqlParameter("company",MySqlDbType.VarChar){ Value=jH.Company},
                    new MySqlParameter("jobnum",MySqlDbType.VarChar){ Value=jH.JobNum},
                    new MySqlParameter("partnum",MySqlDbType.VarChar){ Value=jH.PartNum},
                    new MySqlParameter("prodqty",MySqlDbType.Int32){ Value=jH.ProdQty},
                    new MySqlParameter("productiontype_c",MySqlDbType.VarChar){ Value=jH.ProductionType_c},
                    new MySqlParameter("isrework_c",MySqlDbType.Int32){ Value=jH.IsRework_c?1:0},
                    new MySqlParameter("jobreleased",MySqlDbType.Int32){ Value=jH.JobReleased?1:0},
                    new MySqlParameter("jobclosed",MySqlDbType.Int32){ Value=jH.JobClosed?1:0},
                    new MySqlParameter("ReworkType_c",MySqlDbType.VarChar){ Value=jH.ReworkType_c}
                };
                return _db.GetConnection("SAE_OFN").Ado.ExecuteCommand(sql.Replace(@"[\r\n]", ""), parms.ToArray()) > 1;
            }
            else
            {
                sql = @"INSERT INTO job_head(company,jobnum,partnum,prodqty,productiontype_c,isrework_c,jobreleased,jobclosed,ReworkType_c)
                               ALUES(:company ,:jobnum,:partnum,:prodqty,:productiontype_c,:isrework_c,:jobreleased,:jobclosed,:ReworkType_c)";
                List<OracleParameter> parms = new List<OracleParameter>()
                {
                    new OracleParameter("company",OracleType.VarChar){ Value=jH.Company},
                    new OracleParameter("jobnum",OracleType.VarChar){ Value=jH.JobNum},
                    new OracleParameter("partnum",OracleType.VarChar){ Value=jH.PartNum},
                    new OracleParameter("prodqty",OracleType.Int32){ Value=jH.ProdQty},
                    new OracleParameter("productiontype_c",OracleType.VarChar){ Value=jH.ProductionType_c},
                    new OracleParameter("isrework_c",OracleType.Int32){ Value=jH.IsRework_c?1:0},
                    new OracleParameter("jobreleased",OracleType.Int32){ Value=jH.JobReleased?1:0},
                    new OracleParameter("jobclosed",OracleType.Int32){ Value=jH.JobClosed?1:0},
                    new OracleParameter("ReworkType_c",OracleType.VarChar){ Value=jH.ReworkType_c}
                };
                return _db.GetConnection("OUSB").Ado.ExecuteCommand(sql.Replace(@"[\r\n]", ""), parms.ToArray()) > 1;
            }

        }

        public bool UpdateJobHead(JobHead jH, string type)
        {
            string sql = string.Empty;
            if (type.Contains("OFN"))
            {
                sql = @"UPDATE job_head 
                        SET prodqty=?prodqty,productiontype_c=?productiontype_c,isrework_c=?isrework_c,jobreleased=?jobreleased,jobclosed=?jobclosed,ReworkType_c=?ReworkType_c 
                        WHERE company=?company and jobnum=?jobnum and partnum=?partnum ";
                List<MySqlParameter> parms = new List<MySqlParameter>()
                {
                    new MySqlParameter("company", MySqlDbType.VarChar){ Value=jH.Company},
                    new MySqlParameter("jobnum",MySqlDbType.VarChar){ Value=jH.JobNum},
                    new MySqlParameter("partnum",MySqlDbType.VarChar){ Value=jH.PartNum},
                    new MySqlParameter("prodqty",MySqlDbType.Int32){ Value=jH.ProdQty},
                    new MySqlParameter("productiontype_c",MySqlDbType.VarChar){ Value=jH.ProductionType_c},
                    new MySqlParameter("isrework_c",MySqlDbType.Int32){ Value=jH.IsRework_c ? 1 : 0 },
                    new MySqlParameter("jobreleased",MySqlDbType.Int32){ Value=jH.JobReleased ? 1 : 0 },
                    new MySqlParameter("jobclosed",MySqlDbType.Int32){ Value=jH.JobClosed ? 1 : 0 },
                    new MySqlParameter("ReworkType_c",MySqlDbType.VarChar){ Value=jH.ReworkType_c}
                };
                return _db.GetConnection("SAE_OFN").Ado.ExecuteCommand(sql.Replace(@"[\r\n]", ""), parms.ToArray()) > 1;
            }
            else
            {
                sql = @"UPDATE job_head 
                        SET prodqty=:prodqty,productiontype_c=:productiontype_c,isrework_c=:isrework_c,jobreleased=:jobreleased,jobclosed=:jobclosed,ReworkType_c=:ReworkType_c 
                        WHERE company=:company and jobnum=:jobnum and partnum=:partnum ";

                List<OracleParameter> parms = new List<OracleParameter>()
                {
                    new OracleParameter("company",OracleType.VarChar){ Value=jH.Company},
                    new OracleParameter("jobnum",OracleType.VarChar){ Value=jH.JobNum},
                    new OracleParameter("partnum",OracleType.VarChar){ Value=jH.PartNum},
                     new OracleParameter("prodqty",OracleType.Int32){ Value=jH.ProdQty},
                    new OracleParameter("productiontype_c",OracleType.VarChar){ Value=jH.ProductionType_c},
                    new OracleParameter("isrework_c",OracleType.Int32){ Value=jH.IsRework_c ? 1 : 0 },
                    new OracleParameter("jobreleased",OracleType.Int32){ Value=jH.JobReleased ? 1 : 0 },
                    new OracleParameter("jobclosed",OracleType.Int32){ Value=jH.JobClosed ? 1 : 0 },
                    new OracleParameter("ReworkType_c",OracleType.VarChar){ Value=jH.ReworkType_c}
                };
                return _db.GetConnection("OUSB").Ado.ExecuteCommand(sql.Replace(@"[\r\n]", ""), parms.ToArray()) > 1;

            }
        }

        public bool isExistJobHead(JobHead jH, string type)
        {
            string sql = string.Empty;
            if (type.Contains("OFN"))
            {
                sql = @"select count(1) from job_head where company=?company and jobnum=?jobnum";
                List<MySqlParameter> parms = new List<MySqlParameter>()
                {
                    new MySqlParameter("company",MySqlDbType.VarChar){ Value=jH.Company},
                    new MySqlParameter("jobnum",MySqlDbType.VarChar){ Value=jH.JobNum}
                };
                return _db.GetConnection("SAE_OFN").Ado.SqlQuery<string[]>(sql, parms.ToArray()).Count> 0;
            }
            else
            {
                sql = @"select count(1) from job_head where company=:company and jobnum=:jobnum";
                List<OracleParameter> parms = new List<OracleParameter>()
                {
                    new OracleParameter("company",OracleType.VarChar){ Value=jH.Company},
                    new OracleParameter("jobnum",OracleType.VarChar){ Value=jH.JobNum}
                };
                return _db.GetConnection("OUSB").Ado.SqlQuery<string[]>(sql, parms.ToArray()).Count > 0;
            }
        }

    }
}
